package com.gitee.deeround.jdbchelper.core.dialect.oracle;

import com.gitee.deeround.jdbchelper.core.Dialect;
import com.gitee.deeround.jdbchelper.core.dialect.BaseDialectCore;
import com.gitee.deeround.jdbchelper.out.DataType;
import com.gitee.deeround.jdbchelper.out.DbColumn;
import com.gitee.deeround.jdbchelper.out.DbTable;
import com.gitee.deeround.jdbchelper.util.StringUtils;

import java.util.*;
import java.util.stream.Collectors;

/**
 * Oracle数据库方言实现
 *
 * @author deeround
 * @date 2024/03/29
 */
public class OracleDialect extends BaseDialectCore implements Dialect {

    @Override
    public String getCountSql(String sql) {
        return "SELECT COUNT(*) JH_COUNT FROM ( " + sql + " ) JH_TB ";
    }

    @Override
    public String getPageSql(String sql, int pageNum, int pageSize) {
        return "SELECT * FROM ( SELECT JH_TB.*, ROWNUM JH_ROWNUM FROM ( " + sql + " ) JH_TB ) JH_TB1 WHERE JH_ROWNUM <= " + (pageNum * pageSize) + " AND JH_ROWNUM > " + ((pageNum - 1) * pageSize) + " ";
    }

    @Override
    public Map<DataType, String> getDataType() {
        Map<DataType, String> types = new HashMap<>();
        types.put(DataType.String, "varchar2");
        types.put(DataType.Date, "date");
        types.put(DataType.Clob, "clob");
        types.put(DataType.Blob, "blob");
        types.put(DataType.Integer, "number");
        types.put(DataType.Decimal, "number");
        types.put(DataType.Object, "");
        types.put(DataType.Char, "char");
        return types;
    }

    @Override
    public String getTableColumn(String tableName) {
        return "select columns.column_name AS colName, columns.data_type AS DATATYPE, columns.data_length AS DATALENGTH, columns.data_precision AS DATAPRECISION, " +
                "columns.data_scale AS DATASCALE, columns.nullable AS NULLABLE, columns.column_id AS COLPOSITION, columns.data_default AS DATADEFAULT, comments.comments AS COLCOMMENT," +
                "case when t.column_name is null then 0 else 1 end as COLKEY " +
                "from sys.user_tab_columns columns LEFT JOIN sys.user_col_comments comments ON columns.table_name = comments.table_name AND columns.column_name = comments.column_name " +
                "left join ( " +
                "select col.column_name as column_name, con.table_name as table_name from user_constraints con, user_cons_columns col " +
                "where con.constraint_name = col.constraint_name and con.constraint_type = 'P' " +
                ") t on t.table_name = columns.table_name and columns.column_name = t.column_name " +
                "where columns.table_name = UPPER('" + tableName + "') order by columns.column_id ";
    }

    @Override
    public String existsTable(String tableName) {
        return "select count(*) JH_COUNT from user_tables where upper(table_name)=upper('" + tableName + "')";
    }

    @Override
    public List<String> dropTable(String tableName) {
        return Collections.singletonList("drop table " + tableName);
    }

    @Override
    public List<String> copyTable(String tableName, String newTableName) {
        return Collections.singletonList("CREATE TABLE " + newTableName + " AS SELECT * FROM " + tableName + " where 1=2");
    }

    @Override
    public String existsTableColumn(String tableName, String columnName) {
        return "select count(*) JH_COUNT from user_tab_columns where upper(table_name)=upper('" + tableName + "') and upper(column_name)=upper('" + columnName + "')";
    }

    @Override
    public List<String> createTable(DbTable dbTable) {
        List<String> sqls = new ArrayList<>();
        List<String> keys = new ArrayList<>();

        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE ").append(dbTable.getTableName()).append("(");

        for (int i = 0; i < dbTable.getColumnList().size(); i++) {
            DbColumn dbColumn = dbTable.getColumnList().get(i);
            sb.append(dbColumn.getColumnName()).append(" ");

            String dateTypeStr = getDataType().get(dbColumn.getDataType());
            if (dbColumn.getDataType().equals(DataType.String) || dbColumn.getDataType().equals(DataType.Char)) {
                dateTypeStr += "(" + dbColumn.getDataLength() + ")";
            } else if (dbColumn.getDataType().equals(DataType.Decimal)) {
                dateTypeStr += "(" + dbColumn.getDataLength() + "," + dbColumn.getDataScale() + ")";
            }

            sb.append(dateTypeStr);

            if (!"1".equals(dbColumn.getColumnNullable())) {
                sb.append(" NOT NULL");
            }

            if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
                sb.append(" DEFAULT ").append(dbColumn.getDataDefault());
            }

            if ("1".equals(dbColumn.getColumnKey())) {
                keys.add(dbColumn.getColumnName());
            }

            if (i != dbTable.getColumnList().size() - 1) {
                sb.append(",");
            }
        }

        if (keys.size() > 0) {
            sb.append(",");
            sb.append("PRIMARY KEY (");
            sb.append(StringUtils.join(keys, ","));
            sb.append(")");
        }

        sb.append(")");
        sqls.add(sb.toString());


        //注释
        if (!StringUtils.isEmpty(dbTable.getTableComment())) {
            sqls.add("COMMENT ON TABLE " + dbTable.getTableName() + " IS '" + dbTable.getTableComment() + "'");
        }
        for (DbColumn dbColumn : dbTable.getColumnList()) {
            sqls.add("COMMENT ON COLUMN " + dbTable.getTableName() + "." + dbColumn.getColumnName() + " IS '" + dbColumn.getColumnComment() + "'");
        }

        return sqls;
    }

    @Override
    public List<String> modifyTableComment(String tableName, String tableComment) {
        return Collections.singletonList("comment on table " + tableName + " is '" + tableComment + "'");
    }

    @Override
    public List<String> modifyTableName(String oldTableName, String newTableName, String tableComment) {
        List<String> sqls = new ArrayList<>();
        sqls.add("alter table " + oldTableName + " rename to " + newTableName);
        if (!StringUtils.isEmpty(tableComment)) {
            sqls.addAll(modifyTableComment(newTableName, tableComment));
        }
        return sqls;
    }

    @Override
    public List<String> addTableColumn(DbColumn dbColumn) {
        List<String> sqls = new ArrayList<>();
        String dateTypeStr = resolveDataType(dbColumn.getDataType(), dbColumn.getDataLength(), dbColumn.getDataScale());
        StringBuilder sb = new StringBuilder();
        sb.append("alter table " + dbColumn.getTableName() + " add " + dbColumn.getColumnName() + " " + dateTypeStr);
        if (!"1".equals(dbColumn.getColumnNullable())) {
            sb.append(" not null");
        }
        if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
            sb.append(" default " + dbColumn.getDataDefault());
        }
        sqls.add(sb.toString());
        if (!StringUtils.isEmpty(dbColumn.getColumnComment())) {
            sqls.add("comment on column " + dbColumn.getTableName() + "." + dbColumn.getColumnName() + " is '" + dbColumn.getColumnComment() + "'");
        }
        return sqls;
    }

    @Override
    public List<String> modifyTableColumn(DbColumn dbColumn) {
        List<String> sqls = new ArrayList<>();
        String dateTypeStr = resolveDataType(dbColumn.getDataType(), dbColumn.getDataLength(), dbColumn.getDataScale());
        StringBuilder sb = new StringBuilder();
        sb.append("alter table " + dbColumn.getTableName() + " modify (" + dbColumn.getColumnName() + " " + dateTypeStr);
        if (!"1".equals(dbColumn.getColumnNullable())) {
            sb.append(" not null");
        } else {
            sb.append(" null");
        }
        if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
            sb.append(" default " + dbColumn.getDataDefault());
        } else {
            sb.append(" default null");
        }
        sb.append(")");
        sqls.add(sb.toString());
        if (!StringUtils.isEmpty(dbColumn.getColumnComment())) {
            sqls.add("comment on column " + dbColumn.getTableName() + "." + dbColumn.getColumnName() + " is '" + dbColumn.getColumnComment() + "'");
        } else {
            sqls.add("comment on column " + dbColumn.getTableName() + "." + dbColumn.getColumnName() + " is ''");
        }
        return sqls;
    }

    @Override
    public List<String> removeTableColumn(String tableName, String columnName) {
        return Collections.singletonList("alter table " + tableName + " drop column " + columnName);
    }

    @Override
    public List<String> mergeInto(String tableName, String newData, String idColumn, List<String> insertColumns, List<String> updateColumns) {

        if (newData.toLowerCase().contains("select") && newData.contains(" ")) {
            newData = "select * from (" + newData + ")";
        }

        StringBuilder insertSql1 = new StringBuilder();
        StringBuilder insertSql2 = new StringBuilder();
        StringBuilder updateSql = new StringBuilder();

        for (String insertColumn : insertColumns) {
            insertSql1.append(",").append(insertColumn);
            insertSql2.append(",").append("n.").append(insertColumn);
        }

        for (String updateColumn : updateColumns) {
            updateSql.append(",t.").append(updateColumn).append("=n.").append(updateColumn);
        }


        String sql = "merge into " + tableName + " t "
                + " using (" + newData + ") n "
                + " ON (t." + idColumn + "=n." + idColumn + ") "
                + " when not matched then "
                + " " + "insert (" + insertSql1.substring(1) + ") values(" + insertSql2.substring(1) + ")"
                + " when matched then "
                + " " + "update set " + updateSql.substring(1);


        return new ArrayList<>(Arrays.asList(sql));
    }

    @Override
    public String recursiveSub(String tableName, String idColumn, String pidColumn, List<String> columns, String idValue) {
//        SELECT id, name, parent_id
//        FROM nodes
//        START WITH id = :your_node_id -- 这里替换为你想要查询的节点的ID
//        CONNECT BY PRIOR parent_id = id;

        StringBuilder sb = new StringBuilder();
        sb.append(" WITH cte_" + tableName + " AS ( ")
                .append(" SELECT " + columns.stream().collect(Collectors.joining(",")) + " ")
                .append(" FROM " + tableName + " ")
                .append(" START WITH id = '" + idValue + "' ")
                .append(" CONNECT BY PRIOR " + pidColumn + " = " + idColumn + " ")
                .append(" ) ");

        return sb.toString();

    }

    @Override
    public String funDate() {
        return "sysdate";
    }

    @Override
    public String funTimestamp() {
        return "systimestamp";
    }

    @Override
    public String funGuid() {
        return "sys_guid()";
    }

    @Override
    public String funWmConcat(String columnName) {
        return "wm_concat(" + columnName + ")";
    }
}
